﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlServerCe;
using System.IO;
using System.Configuration;

namespace OWASP.DotNetGoat
{
    public partial class dbtest : System.Web.UI.Page
    {
        string fileName = @".\testdb.sdf";
        //string password = "test";


           
        protected void Page_Load(object sender, EventArgs e)
        {
                        
        }

        protected void btnTest_Click(object sender, EventArgs e)
        {
            try
            {

                DatabaseUtilities du = new DatabaseUtilities();
                lblOutput.Text = "<b>details:</b><br/>" + du.RefreshDatabase();
                lblStatus.Text = "Database creation succeeded at:" + DateTime.Now.ToLongTimeString() + ". Congrats!";
            }
            catch (Exception ex)
            {
                lblStatus.Text = "database creation failed - make sure the database file isn't already open (like in visual studio)";
                lblStatus.Text = "";
            }
            //lblOutput.Text = Server.MapPath("~/data.sql");
            //createDB();
        }

        /*
        void createDB()
        {
            //read in the SQL file, and execute all statements 
            if (File.Exists(fileName))
                File.Delete(fileName);

            SqlCeEngine en = new SqlCeEngine(getConnectString());
            en.CreateDatabase();

            SqlCeConnection cn = new SqlCeConnection(getConnectString());

            if (cn.State == System.Data.ConnectionState.Closed)
                cn.Open();
            
            
            SqlCeCommand cmd; 
            
            

            StreamReader sr = new StreamReader(Server.MapPath("~/data.sql"));
            string sql = sr.ReadToEnd();

            cmd = new SqlCeCommand(sql, cn);

            
            try
            {
                cmd.ExecuteNonQuery();
                lblOutput.Text += sql + " executed<br/>";
            }
            catch (SqlCeException ex)
            {
                lblOutput.Text += "create table SQL Exception: " + ex.Message + "<br/>";
            }
            catch (Exception ex)
            {
                lblOutput.Text += "create table Exception: " + ex.Message + "<br/>";
            }
            finally
            {
                cn.Close();
            }
            
            //::End Loop::
        }
        */


        /*
        protected void btnTest_Click(object sender, EventArgs e)
        {

            createDB();
            createTable("CREATE TABLE MailingList (FirstName nvarchar(50) not null, LastName nvarchar(50), Email nvarchar(256) )");
            createTable("CREATE TABLE Classifieds (Email nvarchar(50) not null, Title nvarchar(50) not null, Message nvarchar(256), Filename nvarchar(256) )");
            createTable("CREATE TABLE Users (UserID INT, UserName NVARCHAR(50) not null, Email NVARCHAR(50), Password NVARCHAR(50))");


            //populatePeopleTable();
            //populateEmailTable();
            //populateGrid();
        }
        */
        string getConnectString()
        {
            //string connectionString = string.Format("Datasource=\"{0}\"; Password='{1}'", fileName, password);
            string connectionString = ConfigurationManager.ConnectionStrings["sqlCEConnString"].ConnectionString;
            return connectionString;
        }
        /*
        void createDB()
        {
            //create a test db
            string connectionString = getConnectString();
            if (File.Exists(fileName))
                File.Delete(fileName);

            SqlCeEngine en = new SqlCeEngine(connectionString);
            en.CreateDatabase();

            lblOutput.Text = "DB Created<br/>";
        }

        void createTable(String SQL)
        {
            SqlCeConnection cn = new SqlCeConnection(getConnectString());

            if (cn.State == System.Data.ConnectionState.Closed)
                cn.Open();


            SqlCeCommand cmd = new SqlCeCommand(SQL, cn);

            try
            {
                cmd.ExecuteNonQuery();
                lblOutput.Text += "Table Created<br/>";
            }
            catch (SqlCeException ex)
            {
                lblOutput.Text += "create table SQL Exception: " + ex.Message + "<br/>";
            }
            catch (Exception ex)
            {
                lblOutput.Text += "create table Exception: " + ex.Message + "<br/>";
            }
            finally
            {
                cn.Close();
            }
        }

        void populatePeopleTable()
        {
            SqlCeConnection cn = new SqlCeConnection(getConnectString());
            if (cn.State == ConnectionState.Closed)
                cn.Open();

            string SQL = "insert into people(LastName, FirstName, URL) values (@lastname, @firstname, @url)";

            try
            {
                addRow(SQL, cn, "Jerry", "Hoff", @"http://jerryhoff.net");
                addRow(SQL, cn, "Jenny", "Hoff", @"http://jennyhoff.net");
                lblOutput.Text += "Records Added<br/>";
            }
            catch (SqlCeException ex)
            {
                lblOutput.Text += "populate table SQL Exception: " + ex.Message + "<br/>";
            }
            catch (Exception ex)
            {
                lblOutput.Text += "populate table Exception: " + ex.Message + "<br/>";
            }
            finally
            {
                cn.Close();
            }
        }

        void populateEmailTable()
        {
            SqlCeConnection cn = new SqlCeConnection(getConnectString());
            if (cn.State == ConnectionState.Closed)
                cn.Open();

            string SQL = "insert into email(LastName, FirstName, Email) values (@lastname, @firstname, @email)";




            try
            {
                addRow(SQL, cn, "Jerry", "Hoff", @"http://jerryhoff.net");
                addRow(SQL, cn, "Jenny", "Hoff", @"http://jennyhoff.net");
                lblOutput.Text += "Records Added<br/>";
            }
            catch (SqlCeException ex)
            {
                lblOutput.Text += "populate table SQL Exception: " + ex.Message + "<br/>";
            }
            catch (Exception ex)
            {
                lblOutput.Text += "populate table Exception: " + ex.Message + "<br/>";
            }
            finally
            {
                cn.Close();
            }
        }


        void addRow(string SQL, SqlCeConnection cn, String p1, String p2, String p3)
        {
            SqlCeCommand cmd = new SqlCeCommand(SQL, cn);
            cmd.Parameters.AddWithValue("@lastname", p2);
            cmd.Parameters.AddWithValue("@firstname", p1);
            cmd.Parameters.AddWithValue("@url", p3);
            cmd.ExecuteNonQuery();
        }

        void populateGrid()
        {
            SqlCeConnection cn = new SqlCeConnection(getConnectString());
            
            if (cn.State == ConnectionState.Closed)
                cn.Open();

                SqlCeCommand cmd = new SqlCeCommand("select * from people", cn);
                SqlCeDataAdapter da = new SqlCeDataAdapter(cmd);
                DataSet ds = new DataSet();
                da.Fill(ds, "test");
                
                grdView.DataSource = ds;
                grdView.DataBind();
            
        }
        */

    }
}